# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
import pymysql
from itemadapter import ItemAdapter


class Scrapyqidianl3Pipeline:
    def process_item(self, item, spider):
        # print("['mon_ticket'] 的类型为" + type(item['mon_ticket']))
        DB_MYSQL = spider.settings.get('DB_MYSQL')
        con = pymysql.connect(**DB_MYSQL)
        cur = con.cursor()
        author_sql = (
            'insert into author(author_id, name, introduction, literature_nums, char_total, write_nums,author_url) values (%s,%s,%s,%s,%s,%s,%s)')
        author_lis = (
            item['author_id'], item['name'], item['introduction'], item['literature_nums'], item['char_total'],
            item['write_nums'], "https://my.qidian.com/author/" + str(item['author_id']))
        book_sql = (
            'insert into book(book_id, title, imgSrc, intro, intro_detail,  latest_chapter, latest_update_time,honor,author_id,mon_ticket,week_reward,today_reward,book_url) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)')
        book_lis = (
            item['book_id'], item['title'], item['imgSrc'], item['intro'], item['intro_detail'], item['latest_chapter'],
            item['latest_update_time'], item['honor'], item['author_id'], item['mon_ticket'], item['week_reward'],
            item['today_reward'], item['url'])

        book_id = item['book_id']
        tag_sql = ('insert into tag(content,tag_type) values (%s,%s)')
        tagrs_sql = ('insert into tag_rs(tag_id,book_id) values (%s,%s)')
        query_sql = ('select tag_id from tag where content = %s')

        try:
            for item in item['tag']:
                query_lis = (item)
                cur.execute(query_sql, query_lis)
                results = cur.fetchall()
                if len(results) == 0:
                    tag_lis = (item, 1)
                    cur.execute(tag_sql, tag_lis)
                    # con.commit()
                    cur.execute(query_sql, query_lis)
                    results = cur.fetchall()
                tag_id = results[0][0]
                tagrs_lis = (tag_id, book_id)
                cur.execute(tagrs_sql, tagrs_lis)
        except Exception as e:
            print("insert err:", e)
            con.rollback()
        else:
            con.commit()

        try:
            cur.execute(book_sql, book_lis)
        except Exception as e:
            print("insert err:", e)
            con.rollback()
        else:
            con.commit()

        try:
            cur.execute(author_sql, author_lis)
        except Exception as e:
            print("insert err:", e)
            con.rollback()
        else:
            con.commit()

        cur.close()
        con.close()
